package diploma.kisel.modelas.reports;

public class ReportsConst {
	
	//Queries
	public static final String queryFindAllPC = "select * from pc";
	public static final String queryFindAllDevice = "select * from device";
	public static final String queryFindAllProgram = "select * from program";
	public static final String queryFindServersByConcept = "Select w.number wsNumber, pc.number pdNumber, '' as typeOfDevice, pc.brand, pc.cost pdCost, 'Компьютеры' as typeOfTable "+
			"from workstation as w, list_pc as lp, pc, Concept c "+
			"where w.id_workstation=lp.id_workstation and "+
				  "pc.id_pc = lp.id_pc and "+
				  "w.id_concept = c.id_concept and "+
				  "w.type = 'Server' and "+
				  "c.id_concept = ? "+
			"UNION "+
			"Select w.number wsNumber, d.number pdNumber, d.id_device_type, d.brand, d.cost pdCost, 'Устройства' as typeOfTable "+
			"from workstation as w, list_device as ld, device as d, Concept c "+
			"where w.id_workstation=ld.id_workstation and "+
				  "d.id_device = ld.id_device and "+
				  "w.id_concept = c.id_concept and "+
				  "w.type = 'Server' and "+
				  "c.id_concept = ? "+
			  "UNION "+
			  "Select w.number wsNumber, db.number pdNumber, 'Общая', '', db.cost, 'Базы данных' as typeOfTable "+
			  "from workstation as w, list_cdb as lcdb, app_database as db, Concept c "+
			  "where w.id_workstation = lcdb.id_workstation and "+
			        "db.id_database = lcdb.id_database and "+
			        "w.id_concept = c.id_concept and "+
			        "w.type = 'Server' and "+
			        "c.id_concept = ? "+
			"order by wsNumber, typeOfTable ";
	
	public static final String queryFindWpByConcept = "Select wp.number wpNumber, pc.number otNumber, '' as typeOrVersion, pc.brand, pc.cost cost, 'Компьютер' as typeOfTable "+
			"from workstation as w, list_pc as lp, pc, Concept c, workplace as wp, list_workplace as lwp "+ 
			"where w.id_workstation=lp.id_workstation and "+
				"pc.id_pc = lp.id_pc and "+
				"w.id_concept = c.id_concept and "+
				"w.id_workstation=lwp.id_workstation and "+
				"wp.id_workplace = lwp.id_workplace and "+
				"(w.type = 'User' or w.type = 'Staff') and "+
				"w.id_concept= ? "+
			"UNION "+
			"Select wp.number wpNumber, d.number otNumber, d.id_device_type, d.brand, d.cost pdCost, 'Устройство' as typeOfTable " +
			"from workstation as w, list_device as ld, device as d, Concept c, workplace as wp, list_workplace as lwp "+
			"where w.id_workstation=ld.id_workstation and "+
				"d.id_device = ld.id_device and "+
				"w.id_concept = c.id_concept and "+
				"w.id_workstation=lwp.id_workstation and "+
				"wp.id_workplace = lwp.id_workplace and "+
				"(w.type = 'User' or w.type = 'Staff') and "+
				"w.id_concept = ? "+
			"UNION "+
			"Select wp.number wsNumber, app.number, '' as typeOrVersion, app.name, app.cost, 'Приложения' "+
			"from workstation as w, list_workplace as lwp, workplace as wp, list_application as lapp, application as app, Concept as c "+
			"where w.id_workstation=lwp.id_workstation and "+
				"wp.id_workplace = lwp.id_workplace and "+
				"wp.id_workplace = lapp.id_workplace and "+
				"app.id_application = lapp.id_application and "+
				"w.id_concept = c.id_concept and "+
				"(w.type = 'User' or w.type = 'Staff') and "+
				"w.id_concept = ? "+
			"UNION "+
			"Select wp.number wsNumber, pr.number, pr.version, pr.name, pr.cost, 'Программы' "+
			"from workstation as w, list_workplace as lwp, workplace as wp, list_program as lpr, program as pr, Concept as c "+
			"where w.id_workstation=lwp.id_workstation and "+
				"wp.id_workplace = lwp.id_workplace and "+
				"wp.id_workplace = lpr.id_workplace and "+
				"pr.id_program = lpr.id_program and "+
				"w.id_concept = c.id_concept and "+
				"(w.type = 'User' or w.type = 'Staff') and "+
				"w.id_concept = ? "+
				"UNION "+
				"Select wp.number wsNumber, db.number, 'Локальная', '', db.cost, 'Базы данных' "+
				"from workstation as w, list_workplace as lwp, workplace as wp, "+
				"list_ldb as lldb, app_database as db, Concept as c "+
				"where w.id_workstation=lwp.id_workstation and "+
				      "wp.id_workplace = lwp.id_workplace and "+
				      "wp.id_workplace = lldb.id_workplace and "+
				      "db.id_database = lldb.id_database and "+
				       "w.id_concept = c.id_concept and "+
				      "(w.type = 'User' or w.type = 'Staff') and "+
				      "w.id_concept = ? "+
				"order by wpNumber, typeOfTable";
	
	//Jrxml
	public static final String jrxmlComputers = "/reports/ComputerReport.jrxml";
	public static final String jrxmlDevices = "/reports/DeviceReport.jrxml";
	public static final String jrxmlProgram = "/reports/ProgramReport.jrxml";
	public static final String jrxmlServers = "/reports/ServerReport3.jrxml";
	public static final String jrxmlWorkplaces = "/reports/WorkplaceReport2.jrxml";

}
